package com.genius.workflow.util;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.text.ParseException;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;
import javax.annotation.PostConstruct;
import javax.sql.DataSource;

import com.baomidou.dynamic.datasource.annotation.DS;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.genius.workflow.config.DefaultConfiguration;
import com.genius.workflow.element.BoolColumn;
import com.genius.workflow.element.BytesColumn;
import com.genius.workflow.element.Column;
import com.genius.workflow.element.DateColumn;
import com.genius.workflow.element.DoubleColumn;
import com.genius.workflow.element.LongColumn;
import com.genius.workflow.element.SQLBean;
import com.genius.workflow.element.StringColumn;
import com.genius.workflow.exception.CommonErrorCode;
import com.genius.workflow.exception.GeniusDataXException;
import com.genius.workflow.page.PageResult;
import com.genius.workflow.page.SqlLang;

@Component
public class DBUtils {
	private static Logger log = LoggerFactory.getLogger(DBUtils.class);
	
	public static DBUtils dbUtils;
	@Autowired
    @Qualifier("dataSource")
    private  DataSource ds;
	
	@PostConstruct
	public void init(){
		dbUtils = this;
	}
		
	 /**
		 * 填充PreparedStatement参数
		 * @param pstmt 要填充参数的PreparedStatement
		 * @param params 填充的参数
		 * @throws SQLException
	 * @throws ParseException 
		 */
		private static void fillStatement(PreparedStatement pstmt, Object... params) throws SQLException{

			if (params == null) {
	            return;
	        }
			ParameterMetaData pmd = pstmt.getParameterMetaData();
	        if (pmd.getParameterCount() < params.length) {
	            throw new SQLException("Too many parameters: expected " + pmd.getParameterCount() + ", was given " + params.length);
	        }
	        JSONArray jj = new JSONArray();
	        for (int i = 0; i < params.length; i++) {
//	            if(StringUtils.equalsIgnoreCase("null", (String)params[i]) ||StringUtils.equalsIgnoreCase("", (String)params[i])){
//		            if(StringUtils.equalsIgnoreCase("null", (String)params[i]))
//		            	params[i] = null;
//		            if(StringUtils.equalsIgnoreCase("", (String)params[i]))
//		            	params[i] = "";
//	            	
		        	if(params[i] != null){
		            pstmt.setObject(i+1, params[i]);
		           // jj.add(params[i]);
	            }
	        }
	       // System.out.println("参数值--》"+jj);    
	 }
		public static JSONArray selectArr(String sql,Object ... params)throws ConnectionException,SQLException,DBClosedException{
			JSONArray arr = null;
			switch(DefaultConfiguration.geniusDBType){
				case MySql:
				case SqlServer:
					arr = selectArr_(sql, null, params);
					break;
				case Oracle:
					arr = selectArr_(sql, DBTools2.getSelectFieldFromSql(sql), params);
					break;
			}
			return arr;
		}
		
		private static JSONArray selectArr_(String sql,String[]fileds,Object ... params)throws ConnectionException,SQLException,DBClosedException{
	 		JSONArray result = new JSONArray();
	 		Connection con = null;
	 		PreparedStatement pstmt = null;
			ResultSet rs = null;
			try{
				//System.out.println("SQL---> \n"+sql);

				con = dbUtils.ds.getConnection();
				con.setAutoCommit(false);
				pstmt = con.prepareStatement(DBTools.remove$(sql),ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
				fillStatement(pstmt, params);
				rs = pstmt.executeQuery();
				pstmt.setFetchSize(10000);
				con.commit();
				ResultSetMetaData metaData = rs.getMetaData();
				int columnCount = metaData.getColumnCount();
				while(rs.next()){
					JSONObject json = new JSONObject();
					for(int i = 1; i <= columnCount; i++){
						Column column = null;
						String name = metaData.getColumnLabel(i);
						String convertName = name;
						int coloumnType = metaData.getColumnType(i);
						switch(coloumnType){
						
							case Types.CHAR:
		                    case Types.NCHAR:
		                    case Types.VARCHAR:
		                    case Types.LONGVARCHAR:
		                    case Types.NVARCHAR:
		                    case Types.LONGNVARCHAR:
		                        String rawData;
		                        rawData = rs.getString(i);
		                        column = new StringColumn(rawData);
		                        break;
	
		                    case Types.CLOB:
		                    case Types.NCLOB:
		                    	column = new StringColumn(rs.getString(i));
		                        break;
	
		                    case Types.SMALLINT:
		                    case Types.TINYINT:
		                    case Types.INTEGER:
		                    case Types.BIGINT:
		                        column = new LongColumn(rs.getString(i));
		                        break;
	
		                    case Types.NUMERIC:
		                    case Types.DECIMAL:
		                        column = new DoubleColumn(rs.getString(i));
//		                        column = new StringColumn(rs.getString(i));
		                        break;
	
		                    case Types.FLOAT:
		                    case Types.REAL:
		                    case Types.DOUBLE:
		                        column = new DoubleColumn(rs.getString(i));
//		                        column = new StringColumn(rs.getString(i));
		                        break;
	
		                    case Types.TIME:
		                    	column = new StringColumn(rs.getString(i));
//		                        column = new DateColumn(rs.getTime(i));
		                        break;
	
		                    // for mysql bug, see http://bugs.mysql.com/bug.php?id=35115
		                    case Types.DATE:
		                        if (metaData.getColumnTypeName(i).equalsIgnoreCase("year")) {
		                        	column = new LongColumn(rs.getInt(i));
		                        } else {
		                            column = new DateColumn(rs.getDate(i));
		                        }
		                        break;
	
		                    case Types.TIMESTAMP:
		                    	column = new StringColumn(rs.getString(i));
//		                        column = new DateColumn(rs.getTimestamp(i));
		                        break;
	
		                    case Types.BINARY:
		                    case Types.VARBINARY:
		                    case Types.BLOB:
		                    case Types.LONGVARBINARY:
		                        column = new BytesColumn(rs.getBytes(i));
		                        break;
	
		                    // warn: bit(1) -> Types.BIT 可使用BoolColumn
		                    // warn: bit(>1) -> Types.VARBINARY 可使用BytesColumn
		                    case Types.BOOLEAN:
		                    case Types.BIT:
		                        column = new BoolColumn(rs.getBoolean(i));
		                        break;
	
		                    case Types.NULL:
		                        String stringData = null;
		                        if(rs.getObject(i) != null) {
		                            stringData = rs.getObject(i).toString();
		                        }
		                        column = new StringColumn(stringData);
		                        break;
	
		                    default:
		                    	column = new StringColumn(rs.getObject(i).toString());
							}
							if(fileds !=null){
								convertName = getSelectField(name, fileds);
							}
							json.put(convertName, column==null?"null":column.asSource());
					}
					result.add(json);
				}
				//System.out.println("返回值---》"+result);
			}catch(ConnectionException e){
				log.error("数据库连接失败,请检查!");
				throw e;
			}catch(SQLException e){
				log.error(String.format("查询【%s】失败,错误原因为【%s】",sql,e.getMessage()));
				throw e;
			}  finally{
				closeDBResources(rs,pstmt, con);
			}
			return result;
	 	}
		private static String getSelectField(String name,String [] fileds){
			String result = "";
			for(String filed:fileds){
				if(StringUtils.equalsIgnoreCase(filed, name)){
					result = filed;
					break;
				}
			}
			return result;
		}
		/**
		 * 分页查询语句
		 * @param sql
		 * @param params
		 * @return
		 * @throws ConnectionException
		 * @throws SQLException
		 * @throws DBClosedException
		 */
		public static PageResult selectByCount(SqlLang sql,Object ... params) throws ConnectionException, DBClosedException, SQLException{
			PageResult page = null;
			switch(DefaultConfiguration.geniusDBType){
				case MySql:
				case SqlServer:
					page = selectByCount(sql, null, params);
					break;
				case Oracle:
					page = selectByCount(sql, DBTools2.getSelectFieldFromSql(sql.getSourceSql()), params);
					break;
			}
			return page;
		}
		private static PageResult selectByCount(SqlLang sql,String [] fileds,Object ... params)throws ConnectionException,SQLException,DBClosedException{
	 		
	 		JSONArray arr = new JSONArray();
	 		PageResult page = new PageResult();
	 		Connection con = null;
	 		PreparedStatement pstmt = null;
			ResultSet rs = null;
			ResultSet rsCount = null;
			try{
				con = dbUtils.ds.getConnection();
				con.setAutoCommit(false);
				pstmt = con.prepareStatement(DBTools.remove$(sql.getPageSql()),ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
				fillStatement(pstmt, params);
				rs = pstmt.executeQuery();
				pstmt.setFetchSize(10000);
				con.commit();
				ResultSetMetaData metaData = rs.getMetaData();
				int columnCount = metaData.getColumnCount();
				while(rs.next()){
					JSONObject json = new JSONObject();
					for(int i = 1; i <= columnCount; i++){
						Column column = null;
						String name = metaData.getColumnLabel(i);
						String convertName = name;
						int coloumnType = metaData.getColumnType(i);
						switch(coloumnType){
						
						case Types.CHAR:
	                    case Types.NCHAR:
	                    case Types.VARCHAR:
	                    case Types.LONGVARCHAR:
	                    case Types.NVARCHAR:
	                    case Types.LONGNVARCHAR:
	                        String rawData;
	                        rawData = rs.getString(i);
	                        column = new StringColumn(rawData);
	                        break;

	                    case Types.CLOB:
	                    case Types.NCLOB:
	                    	column = new StringColumn(rs.getString(i));
	                        break;

	                    case Types.SMALLINT:
	                    case Types.TINYINT:
	                    case Types.INTEGER:
	                    case Types.BIGINT:
	                        column = new LongColumn(rs.getString(i));
	                        break;

	                    case Types.NUMERIC:
	                    case Types.DECIMAL:
	                        column = new DoubleColumn(rs.getString(i));
//	                        column = new StringColumn(rs.getString(i));
	                        break;

	                    case Types.FLOAT:
	                    case Types.REAL:
	                    case Types.DOUBLE:
	                        column = new DoubleColumn(rs.getString(i));
//	                        column = new StringColumn(rs.getString(i));
	                        break;

	                    case Types.TIME:
	                    	column = new StringColumn(rs.getString(i));
//	                        column = new DateColumn(rs.getTime(i));
	                        break;

	                    // for mysql bug, see http://bugs.mysql.com/bug.php?id=35115
	                    case Types.DATE:
	                        if (metaData.getColumnTypeName(i).equalsIgnoreCase("year")) {
	                        	column = new LongColumn(rs.getInt(i));
	                        } else {
	                            column = new DateColumn(rs.getDate(i));
	                        }
	                        break;

	                    case Types.TIMESTAMP:
	                    	column = new StringColumn(rs.getString(i));
//	                        column = new DateColumn(rs.getTimestamp(i));
	                        break;

	                    case Types.BINARY:
	                    case Types.VARBINARY:
	                    case Types.BLOB:
	                    case Types.LONGVARBINARY:
	                        column = new BytesColumn(rs.getBytes(i));
	                        break;

	                    // warn: bit(1) -> Types.BIT 可使用BoolColumn
	                    // warn: bit(>1) -> Types.VARBINARY 可使用BytesColumn
	                    case Types.BOOLEAN:
	                    case Types.BIT:
	                        column = new BoolColumn(rs.getBoolean(i));
	                        break;

	                    case Types.NULL:
	                        String stringData = null;
	                        if(rs.getObject(i) != null) {
	                            stringData = rs.getObject(i).toString();
	                        }
	                        column = new StringColumn(stringData);
	                        break;

	                    default:
	                        throw GeniusDataXException
	                                .asDataXException(
	                                		CommonErrorCode.UNSUPPORTED_TYPE,
	                                        String.format(
	                                                "读取数据库错误，不支持数据库读取这种字段类型. 字段名:[%s], 字段名称:[%s], 字段Java类型:[%s]. 请尝试使用数据库函数将其转换geniusPlatform支持的类型 或者不同步该字段 .",
	                                                metaData.getColumnName(i),
	                                                metaData.getColumnType(i),
	                                                metaData.getColumnClassName(i)));
						}
						if(fileds !=null){
							convertName = getSelectField(name, fileds);
						}
						json.put(convertName, column==null?"null":column.asSource());
					}
					arr.add(json);
				}
				page.setData(arr);
				String sqlCount = "SELECT FOUND_ROWS() as count";
				switch(DefaultConfiguration.geniusDBType){
					case MySql:
						sqlCount = "SELECT FOUND_ROWS() as count";
						rsCount = pstmt.executeQuery(sqlCount);
						break;
					case SqlServer:
						
					case Oracle:
						if(sql.getSourceSql().toLowerCase().contains("select")){
							sqlCount = "SELECT count(1) as count from ("+sql.getSourceSql()+")t";
						}else{
							sqlCount = "SELECT count(1) as count from "+sql.getSourceSql();
						}
						pstmt = con.prepareStatement(DBTools.remove$(sqlCount),ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
						fillStatement(pstmt, params);
						rsCount = pstmt.executeQuery();
						break;
				}
				con.commit();
				while(rsCount.next()){
					page.setCount(rsCount.getLong(1));
				}
			}catch(ConnectionException e){
				log.error("数据库连接失败,请检查!");
				throw e;
			}catch(SQLException e){
				log.error(String.format("查询【%s】失败,错误原因为【%s】",sql,e.getMessage()));
				throw e;
			}  finally{
				closeDBResources(rs,pstmt, con);
			}
			return page;
	 	}
		
	 	public static int [] execute(SQLBean bean)throws ConnectionException,SQLException,DBClosedException {
	 		return executeBatch(new LinkedList(Arrays.asList(bean)));
	 	}
	 	public static int [] executeBatch(LinkedList<SQLBean> beanList)throws ConnectionException,SQLException,DBClosedException {
	 		int[] backResult = null;
			Connection con = null;
			PreparedStatement pstmt = null;
			try{
				con = dbUtils.ds.getConnection();
				con.setAutoCommit(false);
				
				int size = beanList.size();
				for(int index =0;index<size;index++){
					SQLBean bean = beanList.removeFirst();
					Object parms = bean.getParms();
					pstmt = con.prepareStatement(DBTools.remove$(bean.getSql()));
					if(parms == null){
						pstmt.addBatch();
					}
					if(parms instanceof String[]){
						fillStatement(pstmt, (String[])bean.getParms());
						pstmt.addBatch();
					}
					if(parms instanceof List){
						List<String[]> parmsList = (List<String[]> )parms;
						for(int i = 0; i < parmsList.size(); i++){
							fillStatement(pstmt, parmsList.get(i));
							pstmt.addBatch();
							if((i % 5000 == 0)&& i!=0) {
								pstmt.executeBatch();
								con.commit();
							}
						}	
					}
					if((index % 5000 == 0)&& index !=0) {
						pstmt.executeBatch();
						con.commit();
					}
					backResult = pstmt.executeBatch();
				}
				con.commit();
				con.setAutoCommit(true);
				
			}catch(ConnectionException e){
				log.error("警告!:数据库连接失败,请检查!");
				throw e;
			}catch(SQLException e){
				try {
					if(con!=null){
						con.rollback();
						con.setAutoCommit(true);
					}
					log.error(String.format("批量更新数据失败,正在进行回滚,错误原因为【%s】",e.getMessage()));
					throw e;
				} catch (SQLException re) {
					log.error(String.format("批更新数据回滚失败,错误原因【%s】",e.getMessage()));
					throw e;
				}
			}finally{
				closeDBResources(pstmt, con);
			}
			return backResult;
		}
	 	
	 	public static JSONObject select(String sql,Object ... params) throws ConnectionException, DBClosedException, SQLException{
	 		JSONArray arr = selectArr(sql, params);
	 		return arr.size()!=0?arr.getJSONObject(0):null;
	 	}
	 	public static boolean callPro(String procedure) {
			Connection con = null;
			CallableStatement stmt = null;
			boolean success = false;
			try {
				con = dbUtils.ds.getConnection();
				con.setAutoCommit(false);
				stmt = con.prepareCall(procedure);
				success = stmt.execute();
				
				con.commit();
			} catch(ConnectionException e){
				log.error("执行存储过程时【%s】连接失败,请检查!");
			}catch(SQLException e){
				log.error(String.format("执行存储过程【%s】失败,错误原因为【%s】",procedure,e.getMessage()));
			}finally{
				closeDBResources(stmt, con);
			}
			return success;
		}
	 	public static boolean callProByParms(String procedure,Object ... parms) {
			Connection con = null;
			CallableStatement stmt = null;
			boolean success = false;
			try {
				con = dbUtils.ds.getConnection();
				con.setAutoCommit(false);
				stmt = con.prepareCall(procedure);
				if(parms !=null){
					for(int i=0;i<parms.length;i++){
						stmt.setObject(i+1, parms[i]);
					}
				}
				success = stmt.execute();
				
				con.commit();
			} catch(ConnectionException e){
				log.error("执行存储过程时【%s】连接失败,请检查!");
			}catch(SQLException e){
				log.error(String.format("执行存储过程【%s】失败,错误原因为【%s】",procedure,e.getMessage()));
			}finally{
				closeDBResources(stmt, con);
			}
			return success;
		}
	 	public static Connection getConnect() throws SQLException{
	 		Connection con = dbUtils.ds.getConnection();
	 		return con;
	 	}
	 	public static void beginTransaction(Connection con) throws SQLException{
	 		con.setAutoCommit(false);
	 	}
	 	public static void endTransaction(Connection con) throws SQLException{
	 		con.commit();
	 	}
	 	public static ResultSet query(Connection conn, String sql,Object... parms) throws SQLException{
	 		conn.setAutoCommit(false);
	 		PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
			fillStatement(pstmt, parms);
			pstmt.setFetchSize(10000);
			
	        return pstmt.executeQuery();
	 	}
	 	/**
		 * 提交事务
		 * 
		 * @throws SQLException
		 */
		public static boolean commit(Connection con,List<String> sqlList) throws SQLException {
			boolean result=false;
			Statement stmt = con.createStatement();
			try {
				con.setAutoCommit(false);
				for (int i = 0; i < sqlList.size(); i++) {
					stmt.executeUpdate(sqlList.get(i));
				}
				con.commit();
				con.setAutoCommit(true);
				result=true;
			} catch (SQLException ex) {
				ex.printStackTrace();
				System.out
						.println("Transaction commit is Error:" + ex.getMessage());
				try {
					con.rollback();
				} catch (SQLException e) {
					System.out.println("Transaction rollback is Error:"
							+ e.getMessage());
				}
				result=false;
			}
			return result;
		}
		
		/**
		 * 提交一条事务
		 * 
		 * @throws SQLException
		 */
		public static boolean commitOne(Connection con,String sql) throws SQLException {
			boolean result=false;
			try {
				PreparedStatement stmt = con.prepareStatement(sql);
				stmt.executeUpdate();
				con.commit();
				result=true;
			} catch (SQLException ex) {
				System.out
						.println("Transaction commit is Error:" + ex.getMessage());
				try {
					con.rollback();
				} catch (SQLException e) {
					System.out.println("Transaction rollback is Error:"
							+ e.getMessage());
				}
				result=false;
			}
			return result;
		}
	 	private static void closeResultSet(ResultSet rs) {
	        try {
	            if (null != rs) {
	                Statement stmt = rs.getStatement();
	                if (null != stmt) {
	                    stmt.close();
	                    stmt = null;
	                }
	                rs.close();
	            }
	            rs = null;
	        } catch (SQLException e) {
	            throw new IllegalStateException(e);
	        }
	    }
	 	public static void closeDBResources(Statement stmt, Connection conn){
	 		closeDBResources(null, stmt, conn);
	 	}
	 	public static void closeDBResources(Statement stmt){
	 		closeDBResources(null, stmt, null);
	 	}
	 	public static void closeDBResources(ResultSet rs){
	 		closeResultSet(rs);
	 	}
	 	public static void closeDBResources(Connection conn){
	 		closeDBResources(null, null, conn);
	 	}
	 	public static void closeDBResources(ResultSet rs, Connection conn){
	 		closeResultSet(rs);
	 		closeDBResources(null, null, conn);
	 	}
	 	public static void closeDBResources(ResultSet rs, Statement stmt,
			Connection conn) {
	 		try{
	 			if (null != rs) {
	 				rs.close();
	 			}
				if(null != stmt){
					stmt.close();
				}
				if(null != conn){
					conn.close();
				}
			}catch(SQLException e){
				log.error(e.getMessage(), e);
			}
	}
}
